package com.situ.company.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.situ.company.dao.IUserDao;
import com.situ.company.entity.User;
import com.situ.company.util.JDBCUtil;
import com.situ.company.vo.UserInfoVO;

public class UserDaoImpl implements IUserDao {

	@Override
	public List<User> selectAll() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,password,age,email from users";
			statement = connection.prepareStatement(sql);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				int age = resultSet.getInt("age");
				String email = resultSet.getString("email");
				User user = new User(id, name, password, email, age);
				list.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	@Override
	public List<User> selectByPage1(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,password,age,email from users limit ?,?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, offset);
			statement.setInt(2, pageSize);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				int age = resultSet.getInt("age");
				String email = resultSet.getString("email");
				User user = new User(id, name, password, email, age);
				list.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public List<UserInfoVO> selectByPage(String name, int offset, int pageSize) {
		if (name == null) {
			name = "";
		}
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<UserInfoVO> list = new ArrayList<UserInfoVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id AS userId,u.name AS userName,u.age AS userAge,u.email AS userEmail,d.name AS departmentName,GROUP_CONCAT(r.name SEPARATOR ',') AS `roleName`  \r\n" + 
					"FROM (select * from users where name like ?) AS u left JOIN department AS d ON u.department_id=d.id \r\n" + 
					"left JOIN user_role AS ur ON ur.user_id=u.id \r\n" + 
					"left JOIN role AS r ON ur.role_id=r.id GROUP BY u.id ORDER BY u.id  limit ?,? ";
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%" + name + "%");
			statement.setInt(2, offset);
			statement.setInt(3, pageSize);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int userId = resultSet.getInt("userId");
				String userName = resultSet.getString("userName");
				String userEmail = resultSet.getString("userEmail");
				int userAge = resultSet.getInt("userAge");
				String departmentName = resultSet.getString("departmentName");
				String roleName = resultSet.getString("roleName");
				UserInfoVO userInfoVO = new UserInfoVO(userId, userName, userAge, userEmail, departmentName, roleName);
				list.add(userInfoVO);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int selectTotalCount(String name) {
		if (name == null) {
			name = "";
		}
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from users where name like ?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%" + name + "%");
			System.out.println(statement);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				count = resultSet.getInt(1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return count;
	}

	@Override
	public int deleteById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from users where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			count = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		
		return count;
	}

	@Override
	public int insert(User user) {
		Connection connection = null;
		PreparedStatement statement = null;
		int id = 0;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "insert into users(name,age,password,email,department_id) values(?,?,?,?,?)";
			statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, user.getName());
			statement.setInt(2, user.getAge());
			statement.setString(3, user.getPassword());
			statement.setString(4, user.getEmail());
			statement.setInt(5, user.getDepartmentId());
			System.out.println(statement);
			statement.executeUpdate();
			
			// 查询生成的自增的id
			resultSet = statement.getGeneratedKeys();
			if (resultSet != null && resultSet.next()) {
				id = resultSet.getInt(1);
			}
			System.out.println("id: " + id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return id;
	}

	@Override
	public User selectById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,password,age,email,department_id from users where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int userId = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				int age = resultSet.getInt("age");
				String email = resultSet.getString("email");
				int departmentId = resultSet.getInt("department_id");
			    user = new User(userId, name, password, email, age, departmentId);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return user;
	}

	@Override
	public int update(User user) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update users set name=?,age=?,password=?,email=?,department_id=? where id=?";
			statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, user.getName());
			statement.setInt(2, user.getAge());
			statement.setString(3, user.getPassword());
			statement.setString(4, user.getEmail());
			statement.setInt(5, user.getDepartmentId());
			statement.setInt(6, user.getId());
			System.out.println(statement);
			statement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return count;
	}

	

}
